package org.posupe.areas;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.posupe.bdr.PersistenceMechanismException;
import org.posupe.bdr.PersistenceMechanismRDBMS;
import org.posupe.util.RepositorioException;

public class RepositorioAreasBDR implements RepositorioAreas {

	/**
	 * 
	 */
	private static RepositorioAreasBDR instance;

	private PersistenceMechanismRDBMS pm;

	// SQL
	private static String SQL_INSERT = "INSERT INTO AREA (CODIGO, DESCRICAO) VALUES (?,?)";

	private static String SQL_REMOVE = "DELETE FROM AREA WHERE CODIGO = ?";

	private static String SQL_UPDATE = "UPDATE AREA SET DESCRICAO = ? WHERE CODIGO = ?";

	private static String SQL_SELECT = "SELECT * FROM AREA WHERE CODIGO = ?";

	private static String SQL_ALL = "SELECT * FROM AREA";

	private static String SQL_COUNT = "SELECT COUNT(*) FROM AREA WHERE CODIGO = ?";

	private RepositorioAreasBDR() throws RepositorioException {
		try {
			pm = PersistenceMechanismRDBMS.getInstance();
			pm.connect();
		} catch (Exception ex) {
			throw new RepositorioException(ex);
		}
	}

	public synchronized static RepositorioAreasBDR getInstance()
			throws RepositorioException {
		if (instance == null) {
			instance = new RepositorioAreasBDR();
		}
		return instance;
	}

	public void inserir(Area area) throws RepositorioException {
		PreparedStatement statement = null;
		try {
			try {
				statement = (PreparedStatement) pm
						.getCommunicationChannel(SQL_INSERT);
				statement.setInt(1, area.getCodigo());
				statement.setString(2, area.getDescricao());
				statement.executeUpdate();
			} finally {
				if (statement != null) {
					statement.close();
				}
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
	}

	public void remover(int codigo) throws AreaInexistenteException,
			RepositorioException {
		PreparedStatement statement = null;
		try {
			try {
				statement = (PreparedStatement) pm
						.getCommunicationChannel(SQL_REMOVE);
				statement.setInt(1, codigo);
				int i = statement.executeUpdate();
				if (i == 0) {
					throw new AreaInexistenteException(codigo);
				}
			} finally {
				if (statement != null) {
					statement.close();
				}
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
	}

	public Area procurar(int codigo) throws AreaInexistenteException,
			RepositorioException {
		Area area = null;
		PreparedStatement statement = null;
		ResultSet resultset = null;
		try {
			try {
				statement = (PreparedStatement) pm
						.getCommunicationChannel(SQL_SELECT);
				statement.setInt(1, codigo);
				resultset = statement.executeQuery();
				if (resultset.next()) {
					area = new Area(resultset.getInt("CODIGO"), resultset
							.getString("DESCRICAO"));
				} else {
					throw new AreaInexistenteException(codigo);
				}
			} finally {
				if (statement != null) {
					statement.close();
				}
				if (resultset != null) {
					resultset.close();
				}
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
		return area;
	}

	public void atualizar(Area area) throws AreaInexistenteException,
			RepositorioException {
		PreparedStatement statement = null;
		try {
			try {
				statement = (PreparedStatement) pm
						.getCommunicationChannel(SQL_UPDATE);
				statement.setString(1, area.getDescricao());
				statement.setInt(2, area.getCodigo());
				int i = statement.executeUpdate();
				if (i == 0) {
					throw new AreaInexistenteException(area.getCodigo());
				}
			} finally {
				if (statement != null) {
					statement.close();
				}
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
	}

	public boolean existe(int codigo) throws RepositorioException {
		boolean resposta = false;
		PreparedStatement statement = null;
		ResultSet resultset = null;
		int count = 0;
		try {
			try {
				statement = (PreparedStatement) pm
						.getCommunicationChannel(SQL_COUNT);
				statement.setInt(1, codigo);
				resultset = statement.executeQuery();
				if (resultset.next()) {
					count = resultset.getInt(1);
				}
				if (count != 0) {
					resposta = true;
				}
			} finally {
				if (statement != null) {
					statement.close();
				}
				if (resultset != null) {
					resultset.close();
				}
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
		return resposta;
	}

	public IteratorAreas getIterator() throws RepositorioException {
		IteratorAreasArrayList resultado = new IteratorAreasArrayList();
		Statement statement = null;
		ResultSet resultset = null;
		try {
			try {
				statement = (Statement) pm.getCommunicationChannel();
				resultset = statement.executeQuery(SQL_ALL);
				Area area = null;
				while (resultset.next()) {
					area = new Area(resultset.getInt("CODIGO"), resultset
							.getString("DESCRICAO"));
					resultado.add(area);
				}
			} finally {
				if (statement != null) {
					statement.close();
				}
				if (resultset != null) {
					resultset.close();
				}
			}
		} catch (PersistenceMechanismException e) {
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
		return resultado;
	}

	public IteratorAreas procurar(Area area) throws RepositorioException {
		IteratorAreas areas = this.getIterator();
		IteratorAreasArrayList resultado = new IteratorAreasArrayList();
		while (areas.hasNext()) {
			Area areaAtual = areas.next();
			if (areaAtual.match(area)) {
				resultado.add(areaAtual);
			}
		}
		return resultado;
	}

}
